﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Web;
using System.Web.Services;
using SqliteORM;
using TwKarma.Data.Entity;
using System.Configuration;



namespace TwKarma.Web.Service
{
    [WebService(Namespace = "http://tempuri.org/")]
    public class Votes : System.Web.Services.WebService
    {
		/// <summary>
		/// Get Information about Twitter User based on Screenname
		/// </summary>
		/// <param name="screenname">Screenname without leading @</param>
		/// <returns></returns>
        [WebMethod]
        public DTO.TwitterUser UserKarma(string screenname)
        {            
			return DTO.TwitterUser.Get(screenname);
        }
				
        /// <summary>
        /// Get a list of Karma Votes to user
        /// </summary>
        /// <param name="screenname">Twitter Screenname</param>
        /// <param name="page">1 index of page results</param>
        /// <param name="count">count of results per page</param>
        /// <returns></returns>
        [WebMethod]
		public List<DTO.KarmaVote> UserRecieved(string screenname, int page, int count)
		{
			if (String.IsNullOrEmpty(screenname) || page <= 0 || count <= 0)
				throw new ArgumentException("Invalid arguments!");

			TwitterUser user = TwitterUser.Get(screenname);
			if (user == null)
				return null;

			return GetVoteWhere("k.ToId = " + user.TwitterId, page, count);
		}

		/// <summary>
		/// Get a list of Karma Votes from user
		/// </summary>
		/// <param name="screenname">Twitter Screenname</param>
		/// <param name="page">1 index of page results</param>
		/// <param name="count">count of results per page</param>
		/// <returns></returns>
		[WebMethod]
		public List<DTO.KarmaVote> UserSent(string screenname, int page, int count)
		{
			if (String.IsNullOrEmpty(screenname) || page <= 0 || count <= 0)
				throw new ArgumentException("Invalid arguments!");

			TwitterUser user = TwitterUser.Get(screenname);
			if (user == null)
				return null;

			return GetVoteWhere("k.FromId = " + user.TwitterId, page, count);
		}


		/// <summary>
		/// Get a list all KarmaVotes
		/// </summary>
		/// <param name="page">1 index of page results</param>
		/// <param name="count">count of results per page</param>
		/// <returns></returns>
		[WebMethod]
		public List<DTO.Tweet> Global(int page, int count)
		{
            List<DTO.Tweet> votes = new List<DTO.Tweet>();

            using (DirtyBoy dirty = new DirtyBoy())
            {
                dirty.ExecuteNastyPureSql(
                    "SELECT f.TwitterId, f.Score, f.ScreenName, f.ProfileImageUrl, " +
                    "       t.Text, t.StatusId, t.[When] " +
                    "FROM   TwitterUser f " + 
                    "JOIN   Tweet t ON f.TwitterId = t.FromId " +
                    "ORDER BY t.[When] DESC " +
                    "LIMIT  " + ((page - 1) * count) + ", " + count,
                    row =>
                    {
                        votes.Add(new DTO.Tweet()
                        {
                            From = new DTO.TwitterUser() { Id = (int)row[0], Score = (int)row[1], ScreenName = (string)row[2], ProfileImageUrl = (string)row[3] },
                            TweetText = (string)row[4],
                            TweetId = (long)row[5],
                            When = (DateTime)row[6]
                        });
                        return true;
                    }
                );
            }

            return votes;

		}
		
		private List<DTO.KarmaVote> GetVoteWhere(string where, int page, int count)
        {
			List<DTO.KarmaVote> votes = new List<DTO.KarmaVote>();

			using (DirtyBoy dirty = new DirtyBoy())
			{
				dirty.ExecuteNastyPureSql(
					"SELECT f.TwitterId, f.Score, f.ScreenName, f.ProfileImageUrl, " +
					"       s.TwitterId, s.Score, s.ScreenName, s.ProfileImageUrl, " +
					"       t.Text, t.StatusId, " +
					"       k.Score, k.[When] " +
					"FROM   KarmaVote k " +
					"JOIN   TwitterUser f ON f.TwitterId = k.FromId " +
					"JOIN   TwitterUser s ON s.TwitterId = k.ToId " +
					"JOIN   Tweet t       ON t.StatusId  = k.TweetStatusId " +
					"WHERE  " + where + " " +
					"ORDER BY k.[When] DESC " +
					"LIMIT  " + ((page - 1) * count) + ", " + count,
					row =>
					{
						votes.Add(new DTO.KarmaVote()
							{
								From = new DTO.TwitterUser() { Id = (int)row[0], Score = (int)row[1], ScreenName = (string)row[2], ProfileImageUrl = (string)row[3] },
								To = new DTO.TwitterUser() { Id = (int)row[4], Score = (int)row[5], ScreenName = (string)row[6], ProfileImageUrl = (string)row[7] },
								TweetText = (string)row[8],
								TweetId = (long)row[9],
								Score = (int)row[10],
								When = (DateTime)row[11]
							});
						return true;
					}
				);
			}

			return votes;

        }
    }
}
